package c0907m.group2.aitsm.core.db;

import c0907m.group2.aitsm.core.entity.ClassInfo;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Author: LongDH
 * Date: 4/27/11
 * Time: 2:53 PM
 * <p/>
 * Interface for selecting, inserting, updating from Classes table.
 */
public class ClassDb extends BaseDb {
    private String insert = "INSERT INTO classes (name, course_id, course_year_id, description) VALUES (?, ?, ?, ?)";
    private String update = "UPDATE classes SET name = ?, course_id = ?, course_year_id = ?, description = ?, active = ? WHERE id = ?";

    /**
     * Insert a Class into database.
     *
     * @param clas row's information to insert.
     * @throws SQLException if a database access error occurs.
     */
    public void insert(ClassInfo clas) throws SQLException {
        try {
            connect();
            st = con.prepareStatement(insert);

            buildStatement(clas);
            st.executeUpdate();
        } finally {
            close();
        }
    }

    /**
     * Update a Class into database.
     *
     * @param clas row's information to insert.
     * @throws SQLException if a database access error occurs.
     */
    public void update(ClassInfo clas) throws SQLException {
        try {
            connect();
            st = con.prepareStatement(update);

            doUpdate(clas);
        } finally {
            close();
        }
    }

    /**
     * Update a list of Classes into database.
     *
     * @param classes list of row's information to insert.
     * @throws SQLException if a database access error occurs.
     */
    public void update(List<ClassInfo> classes) throws SQLException {
        try {
            connect();
            st = con.prepareStatement(update);

            for (ClassInfo clas : classes) {
                doUpdate(clas);
            }
        } finally {
            close();
        }
    }

    /**
     * Get a Class for the given ID.
     *
     * @param id the row's ID to select.
     * @return row's information found, or null if none.
     * @throws SQLException if a database access error occurs.
     */
    public ClassInfo select(int id) throws SQLException {
        String select = "SELECT * FROM classes WHERE id = " + id;
        try {
            connect();
            st = con.prepareStatement(select);
            rs = st.executeQuery();

            if (rs.next()) {
                return getResult();
            }

            return null;
        } finally {
            close();
        }
    }

    /**
     * Get a list of all Classes.
     *
     * @return list of row's information found.
     * @throws SQLException if a database access error occurs.
     */
    public List<ClassInfo> select() throws SQLException {
        return select(null);
    }

    /**
     * Get a list of Classes for the given name.
     *
     * @param name the row's name to search.
     * @return list of row's information found.
     * @throws SQLException if a database access error occurs.
     */
    public List<ClassInfo> select(String name) throws SQLException {
        return select(name, 0, 0);
    }

    /**
     * Get a list of Classes.
     *
     * @param name           the row's name to search. Null for all.
     * @param course_id      the Course's ID to search. Zero for all.
     * @param course_year_id the Course_Year's ID to search. Zero for all.
     * @return list of row's information found.
     * @throws SQLException if a database access error occurs.
     */
    public List<ClassInfo> select(String name, int course_id, int course_year_id) throws SQLException {
        String select = "SELECT * FROM classes WHERE 1 = 1";
        if (name != null) select += " AND name LIKE '%" + name + "%'";
        if (course_id != 0) select += " AND course_id = " + course_id;
        if (course_year_id != 0) select += " AND course_year_id = " + course_year_id;

        try {
            connect();
            st = con.prepareStatement(select);
            rs = st.executeQuery();

            List<ClassInfo> list = new ArrayList<ClassInfo>();

            while (rs.next()) {
                list.add(getResult());
            }

            return list;
        } finally {
            close();
        }
    }

    /**
     * Update a data object into database.
     *
     * @param clas the data object.
     * @throws SQLException if a database access error occurs.
     */
    private void doUpdate(ClassInfo clas) throws SQLException {
        buildStatement(clas);
        st.setBoolean(5, clas.isActive());
        st.setInt(6, clas.getId());
        st.executeUpdate();
    }

    /**
     * Build the PreparedStatement from a data object.
     *
     * @param clas fine the data object.
     * @throws SQLException if a database access error occurs.
     */
    private void buildStatement(ClassInfo clas) throws SQLException {
        st.setString(1, clas.getName());
        st.setInt(2, clas.getCourseId());
        st.setInt(3, clas.getCourseYearId());
        st.setString(4, clas.getDescription());
    }

    /**
     * Build a data object from ResultSet.
     *
     * @return the data object.
     * @throws SQLException if a database access error occurs.
     */
    private ClassInfo getResult() throws SQLException {
        ClassInfo clas = new ClassInfo();
        clas.setId(rs.getInt(1));
        clas.setName(rs.getString(2));
        clas.setCourseId(rs.getInt(3));
        clas.setCourseYearId(rs.getInt(4));
        clas.setDescription(rs.getString(5));
        clas.setActive(rs.getBoolean(6));

        return clas;
    }
}
